<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>sql server事务 | haijd</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  <meta name="keywords" content="SQL Server" />
  
  
  
  
  <meta name="description" content="SQL Server 事务">
<meta name="keywords" content="SQL Server">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL Server事务">
<meta property="og:url" content="http://www.hais2.com/2015/04/01/SQL-Server-Transaction/index.html">
<meta property="og:site_name" content="haijd">
<meta property="og:description" content="SQL Server 事务">
<meta property="og:locale" content="default">
<meta property="og:image" content="http://ww2.sinaimg.cn/mw690/c55a7aeejw1f1d464k3i0j20o90hq7av.jpg">
<meta property="og:updated_time" content="2017-08-02T15:22:33.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="SQL Server事务">
<meta name="twitter:description" content="SQL Server 事务">
<meta name="twitter:image" content="http://ww2.sinaimg.cn/mw690/c55a7aeejw1f1d464k3i0j20o90hq7av.jpg">
  
    <link rel="alternate" href="/atom.xml" title="haijd" type="application/atom+xml">
  
  <link rel="icon" href="/css/images/favicon.ico">
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link href="https://fonts.googleapis.com/css?family=Open+Sans|Montserrat:700" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Roboto:400,300,300italic,400italic" rel="stylesheet" type="text/css">
  <link href="//cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">
  <style type="text/css">
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/9749f0/00000000000000000001008f/27/l?subset_id=2&fvd=n5) format("woff2");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/90cf9f/000000000000000000010091/27/l?subset_id=2&fvd=n7) format("woff2");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/8a5494/000000000000000000013365/27/l?subset_id=2&fvd=n4) format("woff2");font-weight:lighter;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/d337d8/000000000000000000010095/27/l?subset_id=2&fvd=i4) format("woff2");font-weight:400;font-style:italic;}</style>
    
  <link rel="stylesheet" id="athemes-headings-fonts-css" href="//fonts.googleapis.com/css?family=Yanone+Kaffeesatz%3A200%2C300%2C400%2C700&amp;ver=4.6.1" type="text/css" media="all">
  <link rel="stylesheet" href="/css/style.css">

  <script src="/js/jquery-3.1.1.min.js"></script>

  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="/css/bootstrap.css" >
  <link rel="stylesheet" href="/css/hiero.css" >
  <link rel="stylesheet" href="/css/glyphs.css" >
  
    <link rel="stylesheet" href="/css/vdonate.css" >
  

</head>

<script>
var themeMenus = {};

  themeMenus["/"] = "Home"; 

  themeMenus["/archives"] = "Archives"; 

  themeMenus["/categories"] = "Categories"; 

  themeMenus["/tags"] = "Tags"; 

  themeMenus["/about"] = "About"; 

</script>


  <body data-spy="scroll" data-target="#toc" data-offset="50">


  <header id="allheader" class="site-header" role="banner">
  <div class="clearfix container">
      <div class="site-branding">

          <h1 class="site-title">
            
              <a href="/" title="haijd" rel="home"> haijd </a>
            
          </h1>

          
            <div class="site-description">Stay Hungry,Stay Foolish</div>
          
            
          <nav id="main-navigation" class="main-navigation" role="navigation">
            <a class="nav-open">Menu</a>
            <a class="nav-close">Close</a>
            <div class="clearfix sf-menu">

              <ul id="main-nav" class="nmenu sf-js-enabled">
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/">Home</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/archives">Archives</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/categories">Categories</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/tags">Tags</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/about">About</a> </li>
                    
              </ul>
            </div>
          </nav>


      </div>
  </div>
</header>




  <div id="container">
    <div id="wrap">
            
      <div id="content" class="outer">
        
          <section id="main" style="float:none;"><article id="post-SQL-Server-Transaction" style="width: 66%; float:left;" class="article article-type-post" itemscope itemprop="blogPost" >
  <div id="articleInner" class="clearfix post-1016 post type-post status-publish format-standard has-post-thumbnail hentry category-template-2 category-uncategorized tag-codex tag-edge-case tag-featured-image tag-image tag-template">
    
    
      <header class="article-header">
        
  
    <h1 class="thumb" class="article-title" itemprop="name">
      SQL Server事务
    </h1>
  

      </header>
    
    <div class="article-meta">
      
	Posted on <a href="/2015/04/01/SQL-Server-Transaction/" class="article-date">
	  <time datetime="2015-04-01T13:20:19.000Z" itemprop="datePublished">April 1, 2015</time>
	</a>

      
	<span id="busuanzi_container_page_pv">
	  本文总阅读量<span id="busuanzi_value_page_pv"></span>次
	</span>

    </div>
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="什么是事务"><a href="#什么是事务" class="headerlink" title="什么是事务"></a>什么是事务</h2><p>　　事务(Transaction)是访问并可能更新数据库中的各种数据项的一个程序执行单元。事务用来保持逻辑数据的一致性和可恢复性，避免错误操作引发不可挽回的损失。<br>　　事务由begin Transaction(事务开始)和end Transaction(事务结束)之间执行的全部成员构成。在关系型数据库中，事务是一条或多条SQL语句组成的。</p>
<h3 id="事务的特性"><a href="#事务的特性" class="headerlink" title="事务的特性"></a>事务的特性</h3><p>　　事务有四个属性：原子性、一致性、隔离性、持久性。<a id="more"></a><br>　　● 原子性(atomicity)：一个事务是一个不可分割的工作单位，事务中的操作，要么全部都执行，要么全部都不执行。<br>　　● 一致性(consistency)：事务结束的时候，所有的内部数据必须全部都是正确的。<br>　　● 隔离性(isolation)：并发多个事务时。各个事务之间不干涉内部数据，每一个事务处理的都是另一个事务处理之前或者处理之后的数据。<br>　　● 持久性(durability)：当事务提交后，数据的改变是永久性的，不能在回滚。</p>
<h3 id="事务的种类"><a href="#事务的种类" class="headerlink" title="事务的种类"></a>事务的种类</h3><p>　　自动提交事务：SQL Server中默认的事务模式，将每一条SQL语句看做是一个事务来执行。<br>　　显式事务：使用SQL语句编写，由<code>Begin Transaction</code>开启事务，<code>Commit Transaction</code>提交事务或者<code>Rollback Transaction</code>回滚事务结束事务。<br>　　隐式事务：使用<code>Set IMPLICIT_TRANSACTIONS {ON|OFF}</code>打开或者关闭事务，当为<code>ON</code>时会将隐式事务模式打开，这时就不使用Begin Transaction开启事务了，隐式事务模式会自动启动事务，要做的只是<code>Commit Transaction</code>提交事务、<code>Rollback Transaction</code>回滚事务。</p>
<p>　　<text style="color:yellow">💡<text> 我们常用的事务类型就是显式事务，也就是我们自定义的事务。</text></text></p>
<h2 id="编写事务"><a href="#编写事务" class="headerlink" title="编写事务"></a>编写事务</h2><p>　　常用的关键词语句有4个：<br>　　1. Begin Transaction：标记事务开启。<br>　　2. Commit Transaction：提交事务，表示事务成功执行。<br>　　3. Rollback Transaction：回滚事务，事务的执行过程中出现错误，回滚到事务处理之前的状态。<br>　　4. Save Transaction：设置一个事务保存点，当事务执行出错是，Roolback Transaction语句执行后可以不用全部回滚，可以回滚到这个保存点。当然前提是保证保存点之前的事务不出错。</p>
<h3 id="事务示例代码："><a href="#事务示例代码：" class="headerlink" title="事务示例代码："></a>事务示例代码：</h3><p>　　假设我们有一张员工表，有三个字段分别是Name(nchar)、Age(int)、Class(nchar)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">---开启事务</span></span><br><span class="line"><span class="keyword">begin</span> tran</span><br><span class="line"><span class="comment">--错误捕捉机制</span></span><br><span class="line"><span class="keyword">begin</span> try  </span><br><span class="line">   <span class="comment">--正确</span></span><br><span class="line">   <span class="keyword">insert</span> <span class="keyword">into</span> Staff (<span class="keyword">Name</span>,Age,<span class="keyword">Class</span>) <span class="keyword">values</span> (<span class="string">'Microsoft'</span>,<span class="number">30</span>,<span class="string">'wp部门'</span>)</span><br><span class="line">   <span class="comment">--正确</span></span><br><span class="line">   <span class="keyword">insert</span> <span class="keyword">into</span> Staff (<span class="keyword">Name</span>,Age,<span class="keyword">Class</span>) <span class="keyword">values</span> (<span class="string">'Google'</span>,<span class="number">20</span>,<span class="string">'Android部门'</span>)</span><br><span class="line">   <span class="comment">--因为年龄Age字段是int类型，这里插入了字符串，错误</span></span><br><span class="line">   <span class="comment">--insert into Staff (Name,Age,Class) values ('Apple','abc','ios部门')</span></span><br><span class="line"><span class="keyword">end</span> try</span><br><span class="line"><span class="keyword">begin</span> catch</span><br><span class="line">   <span class="keyword">select</span> Error_number() <span class="keyword">as</span> ErrorNumber,  <span class="comment">--出现错误的代码</span></span><br><span class="line">          Error_severity() <span class="keyword">as</span> ErrorSeverity,  <span class="comment">--错误严重级别，级别小于10 try catch 捕获不到</span></span><br><span class="line">          Error_state() <span class="keyword">as</span> ErrorState ,  <span class="comment">--错误状态码</span></span><br><span class="line">          Error_Procedure() <span class="keyword">as</span> ErrorProcedure , <span class="comment">--出现错误的存储过程或触发器的名称。</span></span><br><span class="line">          Error_line() <span class="keyword">as</span> ErrorLine,  <span class="comment">--发生错误的行号</span></span><br><span class="line">          Error_message() <span class="keyword">as</span> ErrorMessage  <span class="comment">--错误的具体信息</span></span><br><span class="line">   <span class="keyword">if</span>(@@trancount&gt;<span class="number">0</span>) <span class="comment">--全局变量@@trancount，事务时开启此值+1，用来判断是否开启事务</span></span><br><span class="line">      <span class="keyword">rollback</span> tran  <span class="comment">---由于第三条语句出错，这里回滚到开始，前两条语句也没有执行成功</span></span><br><span class="line"><span class="keyword">end</span> catch</span><br><span class="line"><span class="keyword">if</span>(@@trancount&gt;<span class="number">0</span>)</span><br><span class="line"><span class="keyword">commit</span> tran  <span class="comment">--没有发生错误就提交事务。</span></span><br></pre></td></tr></table></figure>
<p><img src="http://ww2.sinaimg.cn/mw690/c55a7aeejw1f1d464k3i0j20o90hq7av.jpg" alt=""></p>

      
    </div>
    <footer class="entry-meta entry-footer">
      
	<span class="ico-folder"></span>
    <a class="article-category-link" href="/categories/SQL/">SQL</a>

      
  <span class="ico-tags"></span>
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/SQL-Server/">SQL Server</a></li></ul>

      
        <div id="donation_div"></div>

<script src="/js/vdonate.js"></script>
<script>
var a = new Donate({
  title: '如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作!', // 可选参数，打赏标题
  btnText: 'Donate', // 可选参数，打赏按钮文字
  el: document.getElementById('donation_div'),
  wechatImage: '/css/images/wechatPay.png',
  alipayImage: '/css/images/alipay.jpg'
});
</script>
      

      
        
	<section id="comments" class="comment">
	  <div id="disqus_thread">
	  <noscript>Please enable JavaScript to view the <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
	  </div>
	</section>

	<script type="text/javascript">
	var disqus_shortname = 'haijdblog';
	(function(){
	  var dsq = document.createElement('script');
	  dsq.type = 'text/javascript';
	  dsq.async = true;
	  dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
	  (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
	}());
	(function(){
	  var dsq = document.createElement('script');
	  dsq.type = 'text/javascript';
	  dsq.async = true;
	  dsq.src = '//' + disqus_shortname + '.disqus.com/count.js';
	  (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
	}());
	</script>


      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2015/04/06/ASP-NET-stored-procedure-operations/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          ASP.NET存储过程操作
        
      </div>
    </a>
  
  
    <a href="/2015/03/26/Sql-Stored-procedure/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">存储过程</div>
    </a>
  
</nav>

  
</article>

<!-- Table of Contents -->

  <aside id="sidebar">
    <div id="toc" class="toc-article">
    <strong class="toc-title">Contents</strong>
    
      <ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#什么是事务"><span class="nav-number">1.</span> <span class="nav-text">什么是事务</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#事务的特性"><span class="nav-number">1.1.</span> <span class="nav-text">事务的特性</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#事务的种类"><span class="nav-number">1.2.</span> <span class="nav-text">事务的种类</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#编写事务"><span class="nav-number">2.</span> <span class="nav-text">编写事务</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#事务示例代码："><span class="nav-number">2.1.</span> <span class="nav-text">事务示例代码：</span></a></li></ol></li></ol>
    
    </div>
  </aside>
</section>
        
      </div>
      <footer id="footer" class="site-footer">
  

  <div class="clearfix container">
      <div class="site-info">
	      &copy; 2018 haijd All Rights Reserved.
          
            <span id="busuanzi_container_site_uv">
              本站访客数<span id="busuanzi_value_site_uv"></span>人次  
              本站总访问量<span id="busuanzi_value_site_pv"></span>次
            </span>
          
      </div>
      <div class="site-credit">
        Theme by <a href="https://github.com/iTimeTraveler/hexo-theme-hiero" target="_blank">hiero</a>
      </div>
  </div>
</footer>


<!-- min height -->

<script>
    var contentdiv = document.getElementById("content");

    contentdiv.style.minHeight = document.body.offsetHeight - document.getElementById("allheader").offsetHeight - document.getElementById("footer").offsetHeight + "px";
</script>
    </div>
    <!-- <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
    <a href="/categories" class="mobile-nav-link">Categories</a>
  
    <a href="/tags" class="mobile-nav-link">Tags</a>
  
    <a href="/about" class="mobile-nav-link">About</a>
  
</nav> -->
    

<!-- mathjax config similar to math.stackexchange -->

<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    tex2jax: {
      inlineMath: [ ['$','$'], ["\\(","\\)"] ],
      processEscapes: true
    }
  });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
    });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for(i=0; i < all.length; i += 1) {
            all[i].SourceElement().parentNode.className += ' has-jax';
        }
    });
</script>

<script type="text/javascript" src="https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/scripts.js"></script>
<script src="/js/bootstrap.js"></script>
<script src="/js/main.js"></script>








	<script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
	</script>






  </div>

  <a id="rocket" href="#top" class=""></a>
  <script type="text/javascript" src="/js/totop.js" async=""></script>
</body>
</html>
